package co.gov.medellin.ssti.sub.model.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import co.gov.medellin.ssti.sub.model.dao.ISecretariaDAO;
import co.gov.medellin.ssti.sub.model.dao.exception.DAOException;
import co.gov.medellin.ssti.sub.model.vo.AreaVO;

public class SecretariaDAOImpl extends BaseDAOImpl implements ISecretariaDAO {

	// **************************************
	// QUERYS
	// **************************************
	private static final String SELECT_TODAS_SECRETARIAS = "SELECT AREA.CODIGO CODIGO, AREA.NOMBRE NOMBRE "
			+ "FROM TCS_BEN_AREA AREA WHERE AREA.AREA_CODIGO IS NULL "
			+ "ORDER BY AREA.NOMBRE";

	private static final String SELECT_BY_USER = "SELECT A.CODIGO CODIGO, A.NOMBRE NOMBRE "
			+ " FROM TCS_BEN_AREA A WHERE A.CODIGO = ANY(SELECT PRO.AREA_CODIGO "
			+ "FROM TCS_BEN_PROGRAMA PRO WHERE (PRO.VIGENCIA = 'A' OR SYSDATE BETWEEN PRO.DESDE_VIGENCIA AND  PRO.HASTA_VIGENCIA) "
			+ "AND PRO.USU_CODIGO = ? "
			+ // PARAMETRO
			"UNION SELECT AREA.AREA_CODIGO "
			+ "FROM TCS_BEN_SUBPROGRAMA SUB, TCS_BEN_AREA AREA "
			+ "WHERE SUB.AREA_CODIGO = AREA.CODIGO "
			+ "AND (SUB.VIGENCIA = 'A' OR SYSDATE BETWEEN SUB.DESDE_VIGENCIA AND  SUB.HASTA_VIGENCIA) "
			+ "AND SUB.USU_CODIGO = ? "
			+ // PARAMETRO
			"UNION SELECT CAMPO_N1 FROM PAOYER.TCS_USUARIO "
			+ "WHERE CAMPO_N1 IS NOT NULL AND CODIGO = ? "
			+ "UNION  SELECT AREA.AREA_CODIGO "
			+ // PARAMETRO
			"FROM TCS_BEN_SUBPROGRAMA_USUARIO SUBUS, TCS_BEN_SUBPROGRAMA SUB, TCS_BEN_AREA AREA "
			+ "WHERE SUBUS.SBPR_CODIGO = SUB.CODIGO AND SUB.AREA_CODIGO = AREA.CODIGO "
			+ "AND (SUB.VIGENCIA = 'A' OR SYSDATE BETWEEN SUB.DESDE_VIGENCIA AND  SUB.HASTA_VIGENCIA) "
			+ "AND SUBUS.USU_CODIGO = ? ) ORDER BY A.NOMBRE"; // PARAMETRO

	public SecretariaDAOImpl(Connection con) {
		super(con);
	}

	/**
	 * 
	 */
	public List<AreaVO> loadAllSecretarias() throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		List<AreaVO> secretarias = new ArrayList<AreaVO>();

		try {
			pstmt = conn.prepareStatement(SELECT_TODAS_SECRETARIAS);
			rst = pstmt.executeQuery();
			while (rst.next()) {
				AreaVO secretaria = new AreaVO();
				secretaria.setCodearea(rst.getString("CODIGO"));
				secretaria.setNombre(rst.getString("NOMBRE"));
				secretarias.add(secretaria);
			}

		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return secretarias;
	}

	/**
	 * 
	 */
	public List<AreaVO> loadSecretariasByUser(String strCodeUsu)
			throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		List<AreaVO> secretarias = new ArrayList<AreaVO>();

		try {
			pstmt = conn.prepareStatement(SELECT_BY_USER);
			pstmt.setString(1, strCodeUsu);
			pstmt.setString(2, strCodeUsu);
			pstmt.setString(3, strCodeUsu);
			pstmt.setString(4, strCodeUsu);
			rst = pstmt.executeQuery();
			while (rst.next()) {
				AreaVO secretaria = new AreaVO();
				secretaria.setCodearea(rst.getString("CODIGO"));
				secretaria.setNombre(rst.getString("NOMBRE"));
				secretarias.add(secretaria);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return secretarias;
	}

}
